package com.cdut.service;

import com.cdut.model.StuInfo;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Author  kobe
 * Created on 2017-05-24-14:28
 */
@Service
public class ExcelReadService {

        public void readXlsExcel(String filename) throws Exception {
                List<StuInfo> stuList = new ArrayList<>();
                //Excel文件
                HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(ResourceUtils.getFile("D:/vue-project/AuthServer-master/src/main/resources/uploadFile/"+filename)));
                //Excel工作表
                HSSFSheet sheet = wb.getSheetAt(0);
                //表头那一行
                HSSFRow fieldRow = sheet.getRow(0);
                short[] arrFieldIx = new short[5];
                short minColIx = fieldRow.getFirstCellNum();
                short maxColIx = fieldRow.getLastCellNum();
                for(short colIx=minColIx; colIx<maxColIx; colIx++) {
                        HSSFCell fieldCell = fieldRow.getCell(colIx);
                        if(fieldCell == null) {
                                continue;
                        }
                        String fieldVal = fieldCell.getStringCellValue();
                        //学号0，姓名1，性别2，学院3，专业大类4
                        //可加入身份证号字段作为密码。
                        if ( fieldVal.contains("学号") ) {
                                arrFieldIx[0] = colIx;
                        } else if ( fieldVal.contains("姓名") ) {
                                arrFieldIx[1] = colIx;
                        } else if ( fieldVal.contains("性别") ) {
                                arrFieldIx[2] = colIx;
                        } else if ( fieldVal.contains("学院") ) {
                                arrFieldIx[3] = colIx;
                        } else if ( fieldVal.contains("专业") ) {
                                arrFieldIx[4] = colIx;
                        }
                }

                for(int i=1; i<sheet.getLastRowNum()+1; i++) {
                        HSSFRow row = sheet.getRow(i);
                        String stuID = row.getCell(arrFieldIx[0]).getStringCellValue(); //名称
                        String stuName = row.getCell(arrFieldIx[1]).getStringCellValue(); //url
                        String sex = row.getCell(arrFieldIx[2]).getStringCellValue();
                        int score = 81;
                        String stuCollege = row.getCell(arrFieldIx[3]).getStringCellValue();
                        String stuSubject =  row.getCell(arrFieldIx[4]).getStringCellValue();
                        String role = "student";
                        String password = "123456";

                        stuList.add(new StuInfo(stuID, stuName, sex, score, stuCollege, stuSubject, role, password));
                }

                System.out.println("----------共有 " + stuList.size() + " 条数据----------");
                //stuList.forEach(System.out::println);
        }
}
